<?php
include_once "lib/config.php";
include_once "lib/pdomanager.php";

include_once "lib/functions.php";

DB::$dbname=DBNAME;
DB::$hostname=HOST;
DB::$dbusername=USERNAME;
DB::$dbpassword=PASSWORD;
DB::$dbtype=TYPE;
DB::$portnumber=PORTNUMBER;

DB::connect();

if(isset($_FILES['employeefile']))
{
	
	$allowedExts = array("xls", "xlsx");
	$temp = explode(".", $_FILES["employeefile"]["name"]);
	$extension = end($temp);
	if ((($_FILES["employeefile"]["type"] == "application/vnd.ms-excel")
	|| ($_FILES["employeefile"]["type"] == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
	&& ($_FILES["employeefile"]["size"] < 2000000)
	&& in_array($extension, $allowedExts))
	{
		if ($_FILES["employeefile"]["error"] > 0)
		{
			$msg= "Error in file uploading : " . $_FILES["employeefile"]["error"] . "<br>";
		}
		else
		{
		    $filename=$_FILES["employeefile"]["name"];
			if (file_exists("uploaded_files/Excel/" . $_FILES["employeefile"]["name"]))
			{
				$filenamearr=explode(".",$filename);
				$filename="";
				for($i=0;$i<count($filenamearr)-1;$i++)
				{
					$filename.=$filenamearr[$i];
				}
				$filename.="_".time().".".$filenamearr[count($filenamearr)-1];
				
			}
			
			move_uploaded_file($_FILES["employeefile"]["tmp_name"],"uploaded_files/Excel/" .$filename);
			
			$path="uploaded_files/Excel/";
			include_once "excelreader.php";
			
			if(count($dataarr)>0)
			{
				$count=0;
				$wrowcount=0;
				$str="";
				$notuploadedqstarr=array();
                $notuploadedsheetarr=array();
				//$dataarr= $sen_obj->sanitize($dataarr);
				$trainingpanelrolearr=array();
                $trainingpanelrolearr["Executive"]="Trainee";
                $trainingpanelrolearr["Sr. Executive"]="Trainee";
                $trainingpanelrolearr["Trainer"]="Trainer";
                $trainingpanelrolearr["KB Executive"]="KB Executive";
                $trainingpanelrolearr["General Manager"]="General Manager";
                
                $trainingpanelskillsarr=array();
                $trainingpanelskillsarr["Sales"]="Sales";
                $trainingpanelskillsarr["Support"]="Tech Support";
                $trainingpanelskillsarr["V&A Training"]="VNA";
                
                $emptypearr=array();
                $emptypearr["General Manager"]=7;
                $emptypearr["KB Executive"]=1;
                $emptypearr["Call Quality Analyst"]=1;
                $emptypearr["Desktop Engineer"]=1;
                $emptypearr["Manager"]=6;
                $emptypearr["Executive"]=1;
                $emptypearr["Supervisor"]=4;
                $emptypearr["Trainer"]=1;
                $emptypearr["Sr. Executive"]=1;
                $emptypearr["Assistant Manager"]=5;
                
                $empptypearr=array();
                $empptypearr["Operations"]=1;
                $empptypearr["Training"]=1;
                $empptypearr["Quality"]=1;
                $empptypearr["HR"]=3;
                $empptypearr["Logistics"]=2;
                
				//line is commented due to not considering the sheets. Only first sheet will be used.
				//for($i=0;$i<count($dataarr);$i++)
				for($i=0;$i<count($dataarr);$i++)
				{
					if(count($dataarr[$i])>0)
					{
						for($j=0;$j<count($dataarr[$i]);$j++)
						{
							//$sqlstring="insert into questions set ";
							
							$datastring=array();
							$emptyflag=0;
							if(isset($dataarr[$i][$j][1]) && trim($dataarr[$i][$j][1])!="" && trim($dataarr[$i][$j][1])!=null)
							{
								$check_query=$general_obj->isexist_parameter($pdo_obj,"usersdemo",array("ecode"=>trim($dataarr[$i][$j][1])),array());
		
                        		if($check_query==0)
                        		{
                        		  $datastring["ecode"]=trim($dataarr[$i][$j][1]);
								}
								else
								{
									$emptyflag=1;
									$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                    echo "<br> due to ecode sheet : ".($i+1)." row : ".($j+1);
                                    continue;
								}
							}
							else
							{
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to ecode sheet : ".($i+1)." row : ".($j+1);
								continue;
							}
							if(isset($dataarr[$i][$j][2]) && trim($dataarr[$i][$j][2])!="" && trim($dataarr[$i][$j][2])!=null)
							{
								$datastring['first_name']=trim($dataarr[$i][$j][2]);
							}
							else
							{
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to first name sheet : ".($i+1)." row : ".($j+1);
								continue;
							}
							if(isset($dataarr[$i][$j][3]) && trim($dataarr[$i][$j][3])!="" && trim($dataarr[$i][$j][3])!=null )
							{
								$datastring["last_name"]=trim($dataarr[$i][$j][3]);
							}
							
							if(isset($dataarr[$i][$j][4]) && trim($dataarr[$i][$j][4])!="" && trim($dataarr[$i][$j][4])!=null)
							{
								$datastring["email_id"]=trim($dataarr[$i][$j][4]);
							}
							else
							{
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to email sheet : ".($i+1)." row : ".($j+1);
								continue;
							}
							if(isset($dataarr[$i][$j][5]) && trim($dataarr[$i][$j][5])!="" && trim($dataarr[$i][$j][5])!=null)
							{
								$datastring["contact_number"]=trim($dataarr[$i][$j][5]);
							}
							else
							{
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to contact no  sheet : ".($i+1)." row : ".($j+1);
								continue;
							}
                            
							if(isset($dataarr[$i][$j][6]) && trim($dataarr[$i][$j][6])!="" && trim($dataarr[$i][$j][6])!=null)
							{
								$datastring["current_address"]=trim($dataarr[$i][$j][6]);
							}
							else
							{
								
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to address sheet : ".($i+1)." row : ".($j+1);
								continue;
								
							}
							
							if(isset($dataarr[$i][$j][7]) && trim($dataarr[$i][$j][7])!="")
							{
								$datastring["current_city"]=trim($dataarr[$i][$j][7]);
							}
							else
							{
								
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to city sheet : ".($i+1)." row : ".($j+1);
								continue;
								
							}
							
							if(isset($dataarr[$i][$j][8]) && trim($dataarr[$i][$j][8])!="")
							{
								$datastring["current_state"]=trim($dataarr[$i][$j][8]);
							}
							else
							{
								
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to state sheet : ".($i+1)." row : ".($j+1);
								continue;
								
							}
							
							if(isset($dataarr[$i][$j][9]) && trim($dataarr[$i][$j][9])!="" && (trim($dataarr[$i][$j][9])=="Yes" || trim($dataarr[$i][$j][9])=="No"))
							{
								$datastring["cab_avail"]=trim($dataarr[$i][$j][9]);
							}
							else
							{
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to cab avail sheet : ".($i+1)." row : ".($j+1);
								continue;
							}
							
							if(isset($dataarr[$i][$j][10]) && trim($dataarr[$i][$j][10])!="")
							{
								$processinfo=$pdo_obj->selectparameterdataforbothcondwithequal(" processes where "," id  "," status='Active' ",array("process_name"=>trim($dataarr[$i][$j][10]))," order by id asc  ");
		                        
                                
                              
                        		if(count($processinfo)>0)
                        		{
                        		  $datastring["emp_process"]=$processinfo[0]['id'];
								}
								else
								{
									$emptyflag=1;
									$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                    echo "<br> due to process sheet : ".($i+1)." row : ".($j+1);
									continue;
								}
							}
							else
							{
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to process sheet : ".($i+1)." row : ".($j+1);
								continue;
							}
                            
                            
                            if(isset($dataarr[$i][$j][11]) && trim($dataarr[$i][$j][11])!="")
							{
								$deptinfo=$pdo_obj->selectparameterdataforbothcondwithequal(" departments where "," id  "," status='Active' and process_id='".$datastring["emp_process"]."' ",array("dept_name"=>trim($dataarr[$i][$j][11]))," order by id asc  ");
		
                        		if(count($deptinfo)>0)
                        		{
                        		  $datastring["department"]=$deptinfo[0]['id'];
								}
								else
								{
									$emptyflag=1;
									$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                    echo "<br> due to department sheet : ".($i+1)." row : ".($j+1);
									continue;
								}
							}
							else
							{
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to department sheet : ".($i+1)." row : ".($j+1);
								continue;
							}
                            
                            if(isset($dataarr[$i][$j][12]) && trim($dataarr[$i][$j][12])!="")
							{
								$desginfo=$pdo_obj->selectparameterdataforbothcondwithequal(" designations where "," id  "," status='Active' and process_id='".$datastring["emp_process"]."' and dept_id='".$datastring["department"]."' ",array("designation"=>trim($dataarr[$i][$j][12]))," order by id asc  ");
		
                        		if(count($desginfo)>0)
                        		{
                        		  $datastring["emp_designation"]=$desginfo[0]['id'];
								}
								else
								{
									$emptyflag=1;
									$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                    echo "<br> due to designation sheet : ".($i+1)." row : ".($j+1);
									continue;
								}
							}
							else
							{
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to designation sheet : ".($i+1)." row : ".($j+1);
								continue;
							}
                            
                            
                            if(isset($dataarr[$i][$j][15]) && trim($dataarr[$i][$j][15])!="" && (trim($dataarr[$i][$j][15])=="Male" || trim($dataarr[$i][$j][15])=="Female"))
							{
								$datastring["gender"]=trim($dataarr[$i][$j][15]);
							}
							else
							{
								$emptyflag=1;
								$notuploadedqstarr[]=$dataarr[$i][$j][0]." in sheet ".($i+1);
                                echo "<br> due to gender sheet : ".($i+1)." row : ".($j+1);
								continue;
							}
							
                            
							if($emptyflag==0 && count($datastring)>0)
							{
							    $datastring["password"]=md5("12345");
                                $datastring["password_encrypted"]=base64_encode("12345");
                                if(isset($trainingpanelrolearr[trim($dataarr[$i][$j][12])]))
                                {
                                    if($trainingpanelrolearr[trim($dataarr[$i][$j][12])]=="Trainee")
                                    {
                                        $datastring["training_status"]="In Training";
                                    }
                                    else
                                    {
                                        $datastring["training_status"]="Employee";
                                    }
                                    $datastring["designation"]=$trainingpanelrolearr[trim($dataarr[$i][$j][12])];
                                }
                                else
                                {
                                    $datastring["designation"]="Other";
                                }
                                
                                if(trim($dataarr[$i][$j][14])!="NA")
                                {
                                    $datastring["designation"]="Trainee";
                                }
                                else
                                {
                                    $datastring["designation"]="Other";
                                }
                                
                                
                                if(isset($trainingpanelskillsarr[trim($dataarr[$i][$j][11])]))
                                {
                                    $datastring["process"]=$trainingpanelskillsarr[trim($dataarr[$i][$j][11])];
                                }
                                else
                                {
                                    $datastring["process"]="Other";
                                }
                                
                                if($empptypearr[trim($dataarr[$i][$j][10])]>$emptypearr[trim($dataarr[$i][$j][12])])
                                {
                                    $datastring["type_id"]=$empptypearr[trim($dataarr[$i][$j][10])];
                                }
                                else
                                {
                                    $datastring["type_id"]=$emptypearr[trim($dataarr[$i][$j][12])];
                                }
                                
                                
								$datastring["added_by"]=1;
								$datastring["modified_date"]=time();
                                $datastring["updated_by"]=1;
                                
								$count++;
								//$sqlstring.=$datastring;
								$pdo_obj->insertdata("usersdemo",$datastring);
                                //echo "<br>record inserted sheet : ".($i+1)." row : ".($j+1);
                                
                                
							}
							
							
						}
                        echo "<br><br> Next sheet : ";
					}
					else
					{
						$wrowcount++;
					}
				}
				if($wrowcount==count($dataarr))
				{
					$msg="There is no record found in each workbook of excel sheet!";
				}
				else
				{
					$msg="";
					if($count>0)
					{
						$msg="There are total no of ".$count." users added successfully! ";
					}
					$notuploadedqstarr=array_unique($notuploadedqstarr);
					
					$str=implode(",",$notuploadedqstarr);
					if(trim($str)!="")
					{
						if(strstr($str,","))
						{
							$msg.="Users with serial no. ".$str." are not added due to not matching with existing values or required values.";
						}
						else
						{
							$msg.="Users with serial no. ".$str." is not added due to not matching with existing values or required values.";
						}
					}
				}
			}
			else
			{
				$msg="There is no record found in excel sheet!";
			}
			
		}
	}
	else
	{
		$msg= "Invalid file type. Only xls,xlsx type files are allowed!";
	}
}

if(isset($msg))
{
    echo $msg;
}
?>
<html>
    <head>
        <title>Import Employees from Excel</title>
    </head>
    <body>
        <form method="post" enctype="multipart/form-data">
            <input type="file" name="employeefile" id="employeefile"  />
            <input type="submit" value="Upload Employees" />
        </form>
    </body>
</html>